﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;

namespace shop.Web.Excel
{
    /// <summary> excel导出扩展方法 </summary>
    /// <remarks>
    /// 用法
    /// 返回的文件名 = new ExcelExporter().NewSheet("表格名称").AddTitle("大标题").AddSubTitle("副标题").AddList(数据).End("文件名_可以留空");
    /// </remarks>
    public class ExcelExporter
    {
        ///// <summary> 创建新的文件 </summary>
        //public static ExcelExporter Start()
        //{
        //    var excel = new ExcelHelper()
        //    {
        //        ExportConfigs = new List<SheetExportConfig>()
        //    };

        //    return new ExcelExporter(excel); 
        //}




        /// <summary> 创建新的文件 </summary>
        public ExcelExporter NewSheet(string sheetName)
        {
            if (Excel == null)
                Excel = new ExcelHelper();
            if (Excel.ExportConfigs == null)
                Excel.ExportConfigs = new List<SheetExportConfig>();

            //新建一个表格, 并设置成当前
            if (string.IsNullOrEmpty(sheetName))
            {
                //没有名称， 按顺序起名sheet
                var names = Excel.Workbook.Worksheets.Select(a => a.Name).ToArray();
                int i = 1;
                while (true)
                {
                    sheetName = "sheet" + i;
                    if (names.Contains(sheetName) == false)
                        break;
                }
            }

            Excel.CurrentSheet = Excel.Workbook.Worksheets.Add(sheetName);

            Excel.ExportConfigs.Add(new SheetExportConfig(Excel.CurrentSheet));

            return this;
        }



        /// <summary> 创建新的文件 </summary>
        public ExcelExporter UseCurrentSheet()
        {
            if (Excel == null)
                Excel = new ExcelHelper();
            if (Excel.ExportConfigs == null)
                Excel.ExportConfigs = new List<SheetExportConfig>();

            Excel.ExportConfigs.Add(new SheetExportConfig(Excel.CurrentSheet));

            return this;
        }


        /// <summary> 表格 </summary>
        protected ExcelHelper Excel;

        /// <summary> 构造函数 </summary>
        public ExcelExporter(ExcelHelper excel)
        {
            Excel = excel;
        }

        /// <summary> 构造函数 </summary>
        public ExcelExporter()
        {
            this.Excel = new ExcelHelper()
            {
                ExportConfigs = new List<SheetExportConfig>()
            };
        }





        /// <summary> 添加标题 </summary>
        public ExcelExporter AddTitle(string title)
        {
            this.Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].Title = title;
            return this;
        }

        /// <summary> 添加副标题 </summary>
        public ExcelExporter AddSubTitle(string subTitle)
        {
            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].SubTitle = subTitle;
            return this;
        }

        /// <summary> 添加数据 </summary>
        public ExcelExporter AddDataTable(DataTable table)
        {
            var config = Excel.ExportConfigs[Excel.ExportConfigs.Count - 1];
            if (config.Tables == null)
            {
                config.Tables = new List<DataTable>();
            }

            config.Tables.Add(table);
            return this;
        }



        ///// <summary> 添加数据 </summary>
        //public ExcelExporter AddArray(string[,] data, Dictionary<string, string> columns = null)
        //{

        //    this.AddDataTable((new DataHelper()).ConvertToDataTable(data, columns));
        //    return this;
        //}

        ///// <summary> 添加数据 </summary>
        //public ExcelExporter AddArray(string[,] data, IEnumerable<string> columns)
        //{
        //    this.AddDataTable(new DataHelper().ConvertToDataTable(data, columns));
        //    return this;
        //}

        /// <summary> 添加数据 </summary>
        /// <param name="data">数据</param>
        /// <param name="multyColumns">将数据分栏的栏数</param>
        /// <param name="blankInMultyColumns">分栏中间是否要留空白</param>
        public ExcelExporter AddList<T>(IEnumerable<T> data, int multyColumns = 1, bool blankInMultyColumns = true)
        {
            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].DataList = data;
            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].DataListType = typeof(T);  //记录泛型的类型

            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].MultyColumns = multyColumns;
            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].BlankInMultyColumns = blankInMultyColumns;

            return this;
        }

        /// <summary> 添加跨列信息 </summary>
        public ExcelExporter AddSpanColumns(int columnIndex, int span)
        {
            if (Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].SpanColumns == null)
            {
                Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].SpanColumns = new Dictionary<int, int>();
            }

            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].SpanColumns.Add(columnIndex, span);
            return this;
        }


        /// <summary> 不要打印列标题 </summary>
        public ExcelExporter NoPrintColumnHeader()
        {
            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].PrintColumnHeader = false;

            return this;
        }


        /// <summary> 设置列标题 </summary>
        public ExcelExporter AddColumns(Dictionary<string, string> columns)
        {
            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].ColumnsDict = columns;
            return this;
        }

        /// <summary> 设置列标题, 使用DataTable存储多行的列标题 </summary>
        public ExcelExporter AddColumns(DataTable columns)
        {
            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].ColumnsTable = columns;
            return this;
        }

        /// <summary> 指定list数据的导出的成员 </summary>
        public ExcelExporter UseMembers(IEnumerable<MemberInfo> members)
        {
            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].Members = members.ToArray();
            return this;
        }



        /// <summary> 使用样式 </summary>
        public ExcelExporter UseStyle(TableStyles styles)
        {
            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].Style = styles;
            return this;
        }



        /// <summary> 泛型方法的保存 </summary>
        MethodInfo genericMethod = null;


        /// <summary> 保存 </summary>
        public string End(string filePath)
        {
            //filePath = ExcelHelper.GetExcelName(filePath);

            if (string.IsNullOrEmpty(filePath))
            {
                return null;
            }

            //将所有设置保存到表格中
            SheetExportConfig lastCon = null;

            foreach (SheetExportConfig config in Excel.ExportConfigs)
            {
                //前后两个表格相同, 起始行是上一个表格的最后一行加2
                if (lastCon != null && config.Sheet == lastCon.Sheet)
                {
                    config.BeginRowIndex = lastCon.EndRowIndex + 1;
                }
                lastCon = config;


                if (config.DataList != null)
                {
                    //泛型方法的保存, 需要使用反射调用
                    if (genericMethod == null)
                    {
                        genericMethod = typeof(ExcelHelper).GetMethod("SaveConfigGen", new Type[] { typeof(SheetExportConfig) });
                    }

                    //合并生成最终的函数
                    var curMethod = genericMethod?.MakeGenericMethod(config.DataListType);
                    //调用泛型方法
                    curMethod?.Invoke(Excel, new object[] { config });
                }
                else
                {
                    //不是泛型 直接调用保存
                    Excel.SaveConfig(config);
                }

            }

            //保存到文件
            var file = Excel.SaveFile(filePath);

            return file;
        }

        /// <summary> 保存并打开文件 </summary>
        public string EndThenOpen(string filePath)
        {
            var file = End(filePath);
            if (!string.IsNullOrEmpty(file))
                System.Diagnostics.Process.Start(file);

            return file;
        }


        #region 调整格式

        /// <summary> 调整列格式 </summary>
        public ExcelExporter SetColumnFormat(int col, string format)
        {
            var con = Excel.ExportConfigs[Excel.ExportConfigs.Count - 1];
            if (con.ColumnFormats == null)
            {
                con.ColumnFormats = new Dictionary<int, string>();
            }

            if (con.ColumnFormats.ContainsKey(col))
            {
                con.ColumnFormats[col] = format;
            }
            else
            {
                con.ColumnFormats.Add(col, format);
            }

            return this;
        }


        /// <summary> 调整列格式 </summary>
        public ExcelExporter SetColumnFormat(string columnName, string format)
        {
            var con = Excel.ExportConfigs[Excel.ExportConfigs.Count - 1];
            if (con.ColumnFormats == null)
            {
                con.ColumnFormats2 = new Dictionary<string, string>();
            }

            if (con.ColumnFormats2.ContainsKey(columnName))
            {
                con.ColumnFormats2[columnName] = format;
            }
            else
            {
                con.ColumnFormats2.Add(columnName, format);
            }

            return this;
        }


        /// <summary> 设置布尔类型的显式内容 </summary>
        public ExcelExporter SetBoolText(string trueText, string falseText) 
        {
            var con = Excel.ExportConfigs[Excel.ExportConfigs.Count - 1];

            if (!string.IsNullOrEmpty(trueText))
            {
                con.TrueText = trueText;
            }

            if (!string.IsNullOrEmpty(falseText))
            {
                con.FalseText = falseText;
            }
            return this;
        }

        #endregion



        #region 自定义方法

        /// <summary> 加入自定义的方法 </summary>
        public ExcelExporter DoAfterInsertData(Action<SheetExportConfig> action)
        {
            Excel.ExportConfigs[Excel.ExportConfigs.Count - 1].AfterInsertDataAction = action;
            return this;
        }

        #endregion



    }

}
